package com.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class DatabaseHelper {
	
	public static List<String> getDepartments() throws Exception {
		List<String> results = new ArrayList<String>(0);
		Class.forName("org.hsqldb.jdbcDriver");
		Connection con = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/syllabus-schema", "SA", "");
		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery("select short_name from department");
		if(rs != null) {
			while(rs.next()) {
				results.add(rs.getString(1));
			}
		}
		rs.close();
		stmt.close();
		con.close();
		System.out.println("Departments -> " + results);
		return results;
	}
	public static List<String> getYears(String dept) throws Exception {
		List<String> results = new ArrayList<String>(0);
		Class.forName("org.hsqldb.jdbcDriver");
		Connection con = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/syllabus-schema", "SA", "");
		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery("select yd.year from year_dept yd, department d where yd.d_id = d.id and d.short_name = '" + dept + "'");
		if(rs != null) {
			while(rs.next()) {
				results.add(rs.getString(1));
			}
		}
		rs.close();
		stmt.close();
		con.close();
		System.out.println("YEARS -> " + results);
		return results;
	}
	public static List<String> getSemesters(String year) throws Exception {
		List<String> results = new ArrayList<String>(0);
		Class.forName("org.hsqldb.jdbcDriver");
		Connection con = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/syllabus-schema", "SA", "");
		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery("select semester from year_semester where year = " + year);
		if(rs != null) {
			while(rs.next()) {
				results.add(rs.getString(1));
			}
		}
		rs.close();
		stmt.close();
		con.close();
		System.out.println("semester -> " + results);
		return results;
	}
	public static List<String> getSubjects(String semester, String dept) throws Exception {
		List<String> results = new ArrayList<String>(0);
		Class.forName("org.hsqldb.jdbcDriver");
		Connection con = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/syllabus-schema", "SA", "");
		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery("select subj.subj_code from subject subj,department dept,dept_sem_subject dss where dept.id = dss.dept_id and dss.subject_id = subj.id and dss.semester_id =" + semester +" and dept.short_name ='" + dept + "'");

		if(rs != null) {
			while(rs.next()) {
				results.add(rs.getString(1));
			}
		}
		rs.close();
		stmt.close();
		con.close();
		System.out.println("semester -> " + results);
		return results;
	}
	
	public static void main(String[] args) throws Exception {
		System.out.println(DatabaseHelper.getDepartments() );
		System.out.println(DatabaseHelper.getYears("CSE"));
		System.out.println(DatabaseHelper.getSemesters("1"));
		System.out.println(DatabaseHelper.getSubjects("3", "CSE"));
	}

}
